<?php
/*
Copyright (c) 2007 Adam Carter

For complete licensing information, see LICENSE.txt found in the root folder
*/
require_once 'Zend/Db.php';
require_once 'Zend/Db/Table.php';
require_once 'Zend/Cache.php';
require_once 'DataTable.php';
require_once 'ResultSet.php';
require_once 'DataFunctions/globals.php';
class DataDAL
{
   /**
   * @desc reference to our Zend Framework Database object
   * 
   * @var Zend_Db_Adapter_Abstract
   */
   protected $db;
   
   /**
   * @desc reference to our Zend Framework Cache object
   * 
   * @var Zend_Cache
   */
   protected $cache;
   
   /**
   * @desc An array of Zend_Db_Table_Abstract objects.
   * 
   * This variable isn't heavily used at the moment.  Mainly it's used to 
   * retrieve detailed structural data.  However, this may change at a later
   * date
   * @var array
   */
   protected $tables;
   
   /**
   * @desc constructor method
   * 
   * @param String $dbName the database to connect to.  Default can be found in globals.php
   * @returns void
   */
   public function __construct($dbName = DB_NAME)
   {
   
      //params to open a new DB connection
      $params = array ('host'     => DB_HOST,
                'username' => DB_USER,
                'password' => DB_PASSWORD,
                'dbname'   => $dbName);
      
      //store the db conneciton
      $this->db = Zend_Db::factory('PDO_MYSQL', $params);
      
      //create our cache
      $frontendOptions = array(
         'lifeTime' => CACHE_TIME,                  // cache lifetime of half an hour
         'automaticSerialization' => true 
      );
      $backendOptions = array('cacheDir' => CACHE_DIR);
      $this->cache = Zend_Cache::factory('Output', 'File', $frontendOptions, $backendOptions);
      
      //initialze $tables
      Zend_Db_Table_Abstract::setDefaultAdapter($this->db);
      $tables = array();
   }
   
   /**
   * @desc changes the database that the db connection points to
   * @param String $database the name of the database
   */
   public function selectDatabase($database)
   {
      unset($this->db);
      
      //params to open a new DB connection
      $params = array ('host'     => DB_HOST,
                'username' => DB_USER,
                'password' => DB_PASSWORD,
                'dbname'   => $database);
      
      //store the db conneciton
      $this->db = Zend_Db::factory('PDO_MYSQL', $params);
   }
   
   /**
   * @desc returns the table structure for the provided table
   * 
   * @param String $tableName the name of the table
   * 
   * @returns array
   */
   public function getTableStructure($tableName)
   {
      $tables[$tableName] = new DataTable($tableName);
      return $tables[$tableName]->info();

   }
   
   /**
   * @desc Pulls data dynamically from the specified table
   * 
   * @param String $table the table to be pulled from
   * @param array $where name and value pairs
   * @param boolean $loadFromCache whether or not to store & retrieve the results from the db cache
   * @returns ResultSet
   */
   public function getTableData($table, $where, $loadFromCache = true)
   {
      //reference to our db connection
      $db = &$this->db;
      
      //reference to our cache
      $cache = &$this->cache;
      
      // see if a cache already exists:
      $cacheName = 'getTableData' . $table . '_' . implode("_", $where);
      if( (!$rows = $cache->load(md5($cacheName))) || !$loadFromCache)
      {

         //dynamically build a select statement
         $select = $db->select();
         
         //add the table
         $select->from($table);
         
         //add the where clauses
         foreach($where as $key => $value)
         {
            $select->where($key . ' = ?', $value);
         }
      
         //query the database.
         $result = $db->query(
             $select->__toString(),
            array(
                  )
            );
         
         $rows = $result->fetchAll();
         $cache->save($rows, md5($cacheName));
      } 
      
      return new ResultSet($rows);
   }
   
   /**
   * @desc executes a query
   * 
   * Pretty much an extension to the Zend Framework's query() method, with the
   * only addition of returning a ResultSet object instead of an array
   * 
   * @param String $sql a parameterized SQL statement
   * @param array $params the parameterized values
   * @returns ResultSet
   */
   public function query($sql, $params = array(), $loadFromCache = true)
   {
      //reference to our db connection
      $db = &$this->db;
      
      //reference to our cache
      $cache = &$this->cache;
      
      // see if a cache already exists:
      $cacheName = 'query' . $sql;
      foreach($params as $key => $value)
      {
         $cacheName .= $key . '_' . $value;
      }
      if( (!$rows = $cache->load(md5($cacheName))) || !$loadFromCache)
      {
      
         //query the database.
         $result = $db->query($sql, $params);
         
         $rows = $result->fetchAll();
         $cache->save($rows, md5($cacheName));
      } 
      
      return new ResultSet($rows);
   }
   
   
   /**
   * @desc adds a new record to a database table
   * 
   * Will either return the ID of the row or an error message.
   * 
   * @param String $tableName The name of the table to add a record to
   * @param array $values An associative array of column => value pairs
   * @returns Mixed
   */
   public function insertTableRow($tableName, $values)
   {
      //reference to our db connection
      $db = &$this->db;
      
      //start a transaction
      $db->beginTransaction();
      
      //attempt an insert
      try
      {
         $db->insert($tableName, $values);
         $id = $db->lastInsertId();
         $db->commit();
         return $id;
      }
      catch (Exception $e)
      {
         $db->rollBack();
         return $e->getMessage();
      }
   }
   
   /**
   * @desc updates a row in a table
   * 
   * Will return true if the update was successful or an error message if not
   * 
   * @param String $tableName Where the data will be saved
   * @param array $values An array of 'column' => 'value' pairs
   * @param array $where The where condition on our update
   * @returns Mixed
   */
   public function updateTableRow($tableName, $values, $where)
   {
      //reference to our db connection
      $db = &$this->db;
      
      //start a transaction
      $db->beginTransaction();
      
      //attempt an update
      try
      {
         $whereClause = array();
         foreach($where as $key => $value)
         {
            $whereClause[] = $db->quoteInto($key . ' = ?', $value);
         }
         
         $db->update($tableName, $values, $whereClause);
         $db->commit();
         return true;
      }
      catch (Exception $e)
      {
         $db->rollBack();
         return $e->getMessage();
      }
   }
   
   /**
   * @desc Deletes a row from a database table
   * 
   * Will return true if the update was successful or an error message if not
   * 
   * @param String $tableName Where the data will be saved
   * @param array $where The where condition on our update
   * @returns Mixed
   */
   public function deleteTableRow($tableName, $where)
   {
      //reference to our db connection
      $db = &$this->db;
      
      //start a transaction
      $db->beginTransaction();
      
      //attempt an update
      try
      {
         $whereClause = array();
         foreach($where as $key => $value)
         {
            $whereClause[] = $db->quoteInto($key . ' = ?', $value);
         }
         
         $db->delete($tableName, $whereClause);
         $db->commit();
         return true;
      }
      catch (Exception $e)
      {
         $db->rollBack();
         return $e->getMessage();
      }
   }
}
?>
